I like to put comments in my files containing PL/SQL statements and it recently bit me. Perhaps you have already been bitten by it or knew of this behavior already. SQL*Plus' parser has what I consider a bug. Comments (single and multiline) cannot follow a command terminator (semicolon or slash).
SQL> -- good
SQL> -- good
SQL> select * from dual -- good
2 /
D
-
X
SQL> select * from dual -- good
2 ;
D
-
X
SQL> select * -- good
2 from -- good
3 dual -- good
4 ;
D
-
X
SQL> select * -- good
2 from -- good
3 dual -- good
4 /
D
-
X
SQL> select * from dual; -- bad
2 /
select * from dual; -- bad
*
ERROR at line 1:
ORA-00911: invalid character
SQL> select * from dual
2 / -- bad
3 /
/ -- bad
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
SQL> /* good */
SQL> select * from dual /* good */
2 /
D
-
X
SQL> select * from dual /* good */
2 ;
D
-
X
SQL> select * /* good */
2 from /* good */
3 dual /* good */
4 ;
D
-
X
SQL> select * /* good */
2 from /* good */
3 dual /* good */
4 /
D
-
X
SQL> select * from dual; /* also bad */
2 /
select * from dual; /* also bad */
*
ERROR at line 1:
ORA-00911: invalid character
SQL> select * from dual
2 / /* also bad */
3 /
/ /* also bad */
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
I knew there was a reason I got into the habit of typing semicolons and slashes on a line by themselves. There are probably other weirdnesses too.
Re:Memory jog
Mr. Muskrat on 2007-04-13T15:03:04
I've always put the slash on a line by itself. I had a file with hundreds of lines in it all terminated with semicolons. Recently I went into the file and added some comments. Two of the comments I added after semicolons. This is not the first time I've done this with the same results and I don't want to make this mistake a third time. I made sure to tell everyone in my group about this behavior in the hope that they also won't make the same mistake.
I agree this is bad design, but I think it springs from the subtle difference between the Oracle SQL language and the SQL*Plus program language. Oracle is a madness of interfacing between different languages: PL/SQL, SQL, and SQL*Plus are three separate languages to learn, and they all tend to embed each other. I remember having to explain about three times to a former manager why I could not run an SQL*Plus report from PL/SQL stored in the database before she finally got it.
Strangely enough, even though I believe the SQL standard provides for ; as a statement terminator, I think that / and ; are actually processed only by SQL*Plus. I know for a fact if you pass in a ; in OCI or DBI you'll get an error because Oracle's SQL can't handle it. So, the minute SQL sees ; or / it knows that this "string" is done (the string is a string of characters which are going to be passed to the (PL/)SQL engine), and it goes back to SQL*Plus command mode, and you lose the ability to do PL/SQL comments.